-- @owner: lihongji
-- @date: 2022-03-04
-- @testpoint: USTORE支持group by后跟序列
--step1:建表;expect:成功
drop table if exists t_hash_group_by_028_1;
create table t_hash_group_by_028_1(
staff_id int not null,
course_name varchar(50),
higest_degree varchar(50) not null,
course_start_date date,
course_end_date date,
exam_date date,
score int,
education_note varchar(70),
number1 number(28,8),
b_blooean boolean) with (storage_type=ustore);
drop table if exists t_hash_group_by_028_2;
create table t_hash_group_by_028_2(
staff_id int not null,
higest_degree varchar(8) not null,
graduate_school varchar(64),
graduate_date date,
education_note varchar(70),
number1 number(28,8)) with (storage_type=ustore);
--step2：插入数据;expect:成功
begin
	for i in 15..500 loop
		insert into t_hash_group_by_028_1 values(1,'English','doctor','2017-06-15 12:00:00','2017-06-20 12:00:00','2017-06-25 12:00:00',90,'211',i*i,true);
		insert into t_hash_group_by_028_1 values(2,'Painting','scholar','2018-06-15 12:00:00','2018-06-20 12:00:00','2018-06-25 12:00:00',null,'211',i*i/100,false);
		insert into t_hash_group_by_028_1 values(3,'Drawing','doctor',null,'2018-06-27 12:00:00','2018-06-29 12:00:00',95,'211',ceil(i*i/27),false);
		insert into t_hash_group_by_028_1 values(4,null,'master','2017-06-15 12:00:00','2017-06-20 12:00:00','2017-06-25 12:00:00',90,'211',i*i/100,false);
		insert into t_hash_group_by_028_1 values(5,'System','master','2018-06-15 12:00:00','2018-06-20 12:00:00','2018-06-25 12:00:00',null,'211',i+i+i,true);
		insert into t_hash_group_by_028_1 values(6,'Language','scholar','2018-06-25 12:00:00','2018-06-27 12:00:00','2018-06-29 12:00:00',95,'211',i+999,true);
		insert into t_hash_group_by_028_1 values(7,'GRAMMER','doctor','2017-06-15 12:00:00',null,'2017-06-25 12:00:00',90,'211',i+i-20,false);
		insert into t_hash_group_by_028_1 values(8,'Chinese','scholar','2018-06-15 12:00:00','2018-06-20 12:00:00','2018-06-25 12:00:00',null,'211',i+i,false);
		insert into t_hash_group_by_028_1 values(9,'MATH','doctor','2018-06-25 12:00:00','2018-06-27 12:00:00','2018-06-29 12:00:00',95,'211',i+i,true);
		insert into t_hash_group_by_028_1 values(10,'SQL majorization','doctor','2017-06-15 12:00:00','2017-06-20 12:00:00','2017-06-25 12:00:00',90,'211',i+i,false);
		insert into t_hash_group_by_028_1 values(i-7,'BIG DATA','master','2018-06-15 12:00:00','2018-06-20 12:00:00','2018-06-25 12:00:00',null,'211',i*i/100,false);
		insert into t_hash_group_by_028_1 values(i-6,'Performance Turning','scholar','2018-06-25 12:00:00','2018-06-27 12:00:00',null,95,'211',i+i,true);
		insert into t_hash_group_by_028_1 values(i,'Performance Turning','scholar','2018-06-25 12:00:00','2018-06-27 12:00:00',null,95,'211',i*i/100,false);
	end loop;
end;
/
begin
	for i in 20..700 loop
		insert into t_hash_group_by_028_2 values(3,'doctor','Xidian University','2017-07-06 12:00:00','211',i+i+i);
		insert into t_hash_group_by_028_2 values(4,'doctor','Xidian University','2017-07-06 12:00:00','211',i*i/100);
		insert into t_hash_group_by_028_2 values(5,'master','Northwestern Polytechnical University','2017-07-06 12:00:00','211',i+i);
		insert into t_hash_group_by_028_2 values(6,'scholar','Peking University','2017-07-06 12:00:00','211',i+999);
		insert into t_hash_group_by_028_2 values(7,'scholar','Peking University','2017-07-06 12:00:00','211',i+i+i);
		insert into t_hash_group_by_028_2 values(8,'doctor','Xidian University','2017-07-06 12:00:00','211',i*i/100);
		insert into t_hash_group_by_028_2 values(i-8,'doctor','Xidian University','2017-07-06 12:00:00','211',i+i);
		insert into t_hash_group_by_028_2 values(11,'master','Northwestern Polytechnical University','2017-07-06 12:00:00','211',i*i/100);
		insert into t_hash_group_by_028_2 values(i+2,'scholar','Peking University','2017-07-06 12:00:00','211',i+i+i);
		insert into t_hash_group_by_028_2 values(i-10,'scholar','Peking University','2017-07-06 12:00:00','211',i+999);
		insert into t_hash_group_by_028_2 values(i,'scholar','Peking University','2017-07-06 12:00:00','211',i+i+i);
	end loop;
end;
/
--step3:建索引;expect:成功
drop index if exists ind_hash_group_001;
drop index if exists ind_hash_group_002;
drop index if exists ind_hash_group_003;
create index  ind_hash_group_001 on t_hash_group_by_028_1(staff_id);
create index  ind_hash_group_002 on t_hash_group_by_028_1(course_name);
create index  ind_hash_group_003 on t_hash_group_by_028_1(higest_degree);
drop index if exists ind_hash_group_004;
drop index if exists ind_hash_group_005;
drop index if exists ind_hash_group_006;
create index  ind_hash_group_004 on t_hash_group_by_028_2(staff_id);
create index  ind_hash_group_005 on t_hash_group_by_028_2(education_note);
create index  ind_hash_group_006 on t_hash_group_by_028_2(higest_degree);
--step4:创建序列;expect:成功
drop sequence if exists hash_group_seq;
create sequence hash_group_seq start with 2 increment by 2;
--step5:group by后跟序列查询;expect:成功
select a.higest_degree from t_hash_group_by_028_1 a join t_hash_group_by_028_2 b on a.education_note=b.education_note and a.staff_id=b.staff_id where a.staff_id is not null group by a.higest_degree,hash_group_seq.nextval  order by 1 limit 10;
--step6：清理环境;expect:成功
drop table if exists t_hash_group_by_028_1;
drop table if exists t_hash_group_by_028_2;
